<?php

/** Cms_Resource_CreditSaleProduct_Item */
if (!class_exists('Cms_Resource_CreditSaleProduct_Item')) {
    require_once dirname(__FILE__) . '/CreditSaleProduct/Item.php';
}

/**
 * Cms_Resource_CreditSaleProduct
 *
 * @category   Cms
 * @package    Cms_Model_Resource
 */
class Cms_Resource_CreditSaleProduct extends BIS_Model_Resource_Db_Table_Abstract implements Cms_Resource_CreditSaleProduct_Interface
{
    protected $_name    = 'creditsaleproduct';
    protected $_primary  = 'entryId';
    protected $_rowClass = 'Cms_Resource_CreditSaleProduct_Item';

    protected $_referenceMap = array(
        'Product' => array(
            'columns' => 'saleId',
            'refTableClass' => 'Cms_Resource_CreditSale',
            'refColumns' => 'saleId',
        )
    );
    
    /**
     * Get Sale Product by its entryId
     *
     * @param int $id The id to search for
     * @return Cms_Resource_CreditSaleProduct_Item|null
     */
    public function getCreditSaleProductByEntryId($id)
    {
    	return $this->find($id)->current();
    }

    /**
     * Get Sale Products by its saleId
     *
     * @param int $saleId The sale id to search for
     * @return Zend_Db_Table_Rowset
     */
    public function getCreditSaleProducts($saleId)
    {
        $select = $this->select();

        $select->from('creditsaleproduct')
               ->where("saleId = ?", $saleId);

        return $this->fetchAll($select);
    }

    /**
     * Get Total sum of a product in a credit sale
     *
     * @param int $saleId The sale id to search for
     * @param int $productId The product id to search for in a sale
     * @return int The total product sold
     */
    public function getTotalCreditSaleProductByProductId($saleId, $productId)
    {
        $select = $this->select();

        $select->from('creditsaleproduct',array('SUM(quantity) AS total'))
                ->where("saleId = ?", $saleId)
                ->where("productId = ?", $productId);

        $row = $this->fetchRow($select);

        return $row->total;
    }

    /**
     * Get Total sum of a product sold on credit
     *
     * @param int $productId The product id to search for in a sale
     * @return int The total product sold
     */
    public function getTotalProductSoldByProductId($productId)
    {
        $select = $this->select();

        $select->from('creditsaleproduct',array('SUM(quantity) AS total'))
                ->where("productId = ?", $productId);

        $row = $this->fetchRow($select);

        return $row->total;

    }

     /**
     * Get sales report
      *
     * @param int|null   $productId   product id
     * @param int|null   $branchId    branch id
     * @param array      $data fromDate, toDate
     * @return Zend_Db_Table_Rowset
     */
    public function getSalesReport($productId=null, $branchId=null, $data = array())
    {
        $select = $this->select()
                ->from(array('sp'=>'creditsaleproduct'),
                        array('saleId' => 's.saleId',
                              'invoiceId' => 's.invoiceId',
                              'product' => 'sp.name',
                              'quantity' => 'sp.quantity',
                              'costPrice' => 'sp.costPrice',
                              'salePrice' => 'sp.salePrice',
                              'tax' => 'sp.tax',
                              'discount' => 'sp.discount',
                              'unitPrice' => 'sp.unitPrice',
                              'total' => 'sp.total',
                              'branch' => 's.branch',
                              'soldBy' => 'UPPER(s.soldBy)',
                              'dateSold' => new Zend_Db_Expr('DATE_FORMAT(s.dateSold, "%d-%b-%Y")'),
                              'customerName' => 'UPPER(s.customerName)',
                              'customerPhone' => 's.customerPhone'
                            ))
                ->setIntegrityCheck(false)
                ->join(array('s'=>'creditsale'), 's.saleId = sp.saleId', array('s.saleId'));

        if (!empty($productId)) {
            $select->where("sp.productId = ?", $productId);
        }

        if (!empty($branchId)) {
            $select->where("s.branchId = ?", $branchId);
        }

        if (isset($data['fromDate'])) {
            $fromDate = $data['fromDate'];
            $toDate = (isset($data['toDate'])) ? $data['toDate']: date('Y-m-d');
            $select->where("date(s.dateSold) between '$fromDate' and '$toDate'");

        }

        $select->order('s.dateSold DESC');

        return $this->fetchAll($select);

    }
}